﻿<%@ WebHandler Language="C#" Class="EmployeeReportPageMaint" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using System.Collections;
using ReportBusiness;

/*
 * Description: MES人员配置报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.7.21
 */

public class EmployeeReportPageMaint : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    ADHelper ad = new ADHelper();
    UserImage u = new UserImage();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getEmployeeInfo":
                        r = getEmployeeInfo(jObject);//查询用户信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPageList":
                        r = getPageList(jObject);//查询报表列表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "addPage":
                        r = addPage(jObject);//添加报表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getEmployeePages":
                        r = getEmployeePages(jObject);//查询用户已配置的报表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "deletePage":
                        r = deletePage(jObject);//删除用户配置的报表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getAllPage":
                        r = getAllPage(jObject);//查询所有报表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "batchAddPages":
                        r = batchAddPages(jObject);//批量添加报表
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询用户信息
    Result getEmployeeInfo(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.*
FROM (
SELECT DISTINCT e.employeeid,e.employeename,e.fullname,e.password origin,mp.liuwei,
       er.rolenames,
       f.factoryname,f.factoryid,
       wc.workcentername,wc.workcenterid,
       s.shiftname,nvl(e.shiftid,te.shiftid) shiftid,
       te.teamname,te.teamid,md.menudefinitionname,e.cardnum
FROM employee e 
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid 
LEFT JOIN factory f ON f.factoryid = sv.factoryid 
LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid
LEFT JOIN ( SELECT e.employeeid,listagg(r.rolename,'、') WITHIN GROUP (ORDER BY r.rolename) rolenames  
            FROM employeerole er 
            LEFT JOIN employee e ON e.employeeid = er.employeeid  
            LEFT JOIN roledef r ON r.roleid = er.roleid
            WHERE r.rolename NOT LIKE '%Default%' AND r.rolename NOT IN ('SPC','Portal Configuration','Mfg Audit Trail Inquiry','Security Administration','Login')              
            GROUP BY e.employeeid  
          ) er ON er.employeeid = e.employeeid 
LEFT JOIN ( SELECT te.employeeid,t.shiftid,
                   listagg(t.teamname,'、') WITHIN GROUP (ORDER BY t.teamname) teamname,
                   listagg(t.teamid,'、') WITHIN GROUP (ORDER BY t.teamid) teamid
            FROM teamemployee te 
            LEFT JOIN team t ON t.teamid = te.teamid 
            GROUP BY te.employeeid,t.shiftid
          ) te  ON te.employeeid = e.employeeid AND ROWNUM = 1 
LEFT JOIN shift s ON s.shiftid = NVL(e.shiftid,te.shiftid)
LEFT JOIN mes_userimage_mapping mp ON mp.employeename = e.employeename
LEFT JOIN menudefinition md ON md.menudefinitionid = e.webmenudefinitionid
WHERE 1=1 ";
        if (jObject["employeename"] != null && jObject["employeename"].ToString() != "")
        {
            sql += string.Format(" AND e.employeename = '{0}'", jObject["employeename"].ToString());
        }

        if (jObject["fullname"] != null && jObject["fullname"].ToString() != "")
        {
            sql += string.Format(" AND e.fullname = '{0}'", jObject["fullname"].ToString());
        }

        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray arr = JArray.Parse(jObject["selectOrg"].ToString());
            if (arr.Count == 1)
            {
                sql += string.Format(" AND f.factoryid = '{0}'", arr[0].ToString());
            }
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND f.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND wc.workcenterid = '{0}'", arr[1].ToString());
            }
        }
        
        if (jObject["menuname"] != null && jObject["menuname"].ToString() != "")
        {
            sql += string.Format(" AND md.menudefinitionname = '{0}'", jObject["menuname"].ToString());
        }

        sql += " ) t WHERE 1=1 ";

        if (jObject["rolename"] != null && jObject["rolename"].ToString() != "")
        {
            sql += string.Format(" AND t.rolenames LIKE '%{0}%'", jObject["rolename"].ToString());
        }

        if (jObject["selectOrg"] != null && jObject["selectOrg"].ToString() != "[]")
        {
            JArray arr = JArray.Parse(jObject["selectOrg"].ToString());
            if (arr.Count == 3)
            {
                sql += string.Format(" AND (t.shiftid = '{0}' OR t.teamid LIKE '%{1}%')", arr[2].ToString(), arr[2].ToString());
            }
            if (arr.Count == 4)
            {
                sql += string.Format(" AND t.shiftid = '{0}'", arr[2].ToString());
                sql += string.Format(" AND t.teamid LIKE '%{0}%'", arr[3].ToString());
            }

        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        dt.Columns.Add("password");

        foreach (DataRow dr in dt.Rows)
        {
            string password = "";
            try
            {
                password = ad.Decrypto(dr["origin"].ToString());
            }
            catch (Exception)
            {
                password = dr["origin"].ToString();
            }
            finally
            {
                dr["password"] = password;
            }
        }

        dt.Columns.Add("userimageurl", typeof(string));

        //查找用户头像信息
        dt = u.getUserImage(dt, "userimageurl", "employeename", "liuwei");
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询报表列表
    Result getPageList(JObject jObject)
    {
        string sql = @"
SELECT rp.pageid,rp.pagename,
       CASE WHEN ep.employeeid IS NOT NULL THEN 'true' ELSE 'false' END disabled
FROM mes_reportpage rp
LEFT JOIN mes_employeereportpage ep ON ep.pageid = rp.pageid AND ep.employeeid = '{0}'
WHERE rp.status = 1";
        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //增加报表
    Result addPage(JObject jObject)
    {
        Result r = new Result();
        JArray arr = JArray.Parse(jObject["pageList"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (var item in arr)
        {
            sqlList.Add(string.Format("INSERT INTO mes_employeereportpage(employeeid, pageid) VALUES('{0}', '{1}')", jObject["employeeid"].ToString(), item.ToString()));
        }

        try
        {
            dal.ExecuteSqlTran(sqlList);
            r.data = null;
            r.total = Convert.ToInt32(arr.Count);
            r.meta = new Result.Meta(200, "添加成功");
            return r;
        }
        catch (Exception e)
        {
            r.data = null;
            r.total = Convert.ToInt32(arr.Count);
            r.meta = new Result.Meta(500, e.Message);
            return r;
        }
    }

    //查询用户已配置的报表
    Result getEmployeePages(JObject jObject)
    {
        string sql = @" 
SELECT ep.pageid,rp.pagename
FROM mes_employeereportpage ep 
LEFT JOIN mes_reportpage rp ON rp.pageid = ep.pageid
WHERE rp.status = 1 AND ep.employeeid = '{0}'";
        sql = string.Format(sql, jObject["employeeid"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //删除用户配置的报表
    Result deletePage(JObject jObject)
    {
        JArray arr = JArray.Parse(jObject["pageList"].ToString());
        string sql = "";
        foreach (var item in arr)
        {
            sql = string.Format("DELETE mes_employeereportpage t WHERE t.employeeid = '{0}' AND t.pageid = '{1}'", jObject["employeeid"].ToString(), item.ToString());
            int i = dal.ExecuteSql(sql);
        }
        Result r = new Result();
        r.data = null;
        r.total = Convert.ToInt32(arr.Count);
        r.meta = new Result.Meta(200, "删除成功");
        return r;
    }

    //查询报表列表
    Result getAllPage(JObject jObject)
    {
        string sql = @"SELECT rp.pageid,rp.pagename FROM mes_reportpage rp WHERE rp.status = 1";
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //批量添加
    Result batchAddPages(JObject jObject)
    {
        Result r = new Result();
        JArray pageList = JArray.Parse(jObject["pageList"].ToString());
        JArray personList = JArray.Parse(jObject["personList"].ToString());
        ArrayList sqlList = new ArrayList();
        foreach (var p in personList)
        {
            foreach (var i in pageList)
            {
                string sql = string.Format("SELECT * FROM mes_employeereportpage t WHERE t.employeeid='{0}' AND t.pageid='{1}'", p["employeeid"].ToString(), i.ToString());
                DataTable dt = dal.GetDataTable(sql);
                if (dt.Rows.Count == 0)
                {
                    sqlList.Add(string.Format("INSERT INTO mes_employeereportpage(employeeid, pageid) VALUES('{0}', '{1}')", p["employeeid"].ToString(), i.ToString()));
                }
            }
        }

        if (sqlList.Count == 0)
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(200, "添加成功");
            return r;
        }

        try
        {
            dal.ExecuteSqlTran(sqlList);
            r.data = null;
            r.total = Convert.ToInt32(personList.Count);
            r.meta = new Result.Meta(200, "添加成功");
            return r;
        }
        catch (Exception e)
        {
            r.data = null;
            r.total = Convert.ToInt32(0);
            r.meta = new Result.Meta(500, e.Message);
            return r;
        }
    }
}